#!/usr/bin/python
# -*- encoding: utf-8 -*-
'''
@File    :   
@Time    :   // ::
@Author  :   Malygos 
@Version :   1.0
@Contact :   751756061@qq.com
@WebSite :   https://gitee.com/malygos_l/python_homework
'''
# Start typing your code from here
'''
设计一个留言本的表（ID，留言内容，留言人，留言时间，是否删除）（表名，和字段名自己设计成英文：注意，不要用中文，用中文的直接0分）；
   使用PyMySQL 驱动模块，实现对这个表的增加，删除，修改，查询；数据库操作需要加入异常处理逻辑；
'''

import pymysql


class Excute():

    # 添加
    def insert(self, cursor, db):
        try:
            print("添加数据！")
            id = input("请输入ID：")
            ms = input("请输入留言内容：")
            person = input("留言人：")
            date = input("留言时间：")
            # deleted = input("是否删除：")
            sql = "insert into Message_Book(ID,message,name,date_time) values (\"%s\", \"%s\", \"%s\", \"%s\")" % (
                str(id), str(ms), str(person), str(date))
            # print(sql)
            cursor.execute(sql)
            db.commit()
            print("添加成功！")

        except Exception as e:
            print("添加异常！")
            raise e

    # 删除
    def delete(self, cursor, db):
        try:
            print("删除数据！")
            sql = '''delete from Message_Book where is_delete = 1'''
            cursor.execute(sql)
            db.commit()
            print("删除成功！")

        except Exception as e:
            print("删除异常！")

    # 修改
    def modify(self, cursor, db):
        try:
            print("修改数据！")
            id = input("请输入你想修改的留言人的ID：")
            sql = '''select * from Message_Book where id = %s''' % id
            cursor.execute(sql)
            # rs = cursor.fetchall()
            print("请输入你想修改的内容（message name）：")
            cmd = input()
            if cmd == 'message':
                new_ms = input("请输入修改后的留言内容：")
                sql = '''update Message_Book set message = %s where id = %s''' % (new_ms, id)

            elif cmd == 'name':
                new_name = input("请输入修改后的留言人：")
                sql = '''update Message_Book set message = %s where id = %s''' % (new_name, id)

            # elif cmd == 'date_time':
            #     new_time = input("请输入修改后的留言时间：")
            #     sql = '''update Message Book set message = %s where id = %s''' % (new_time, id)

            # elif cmd == 'is_delete':
            #     new_del = input("请输入修改后的删除状态：")
            #     sql = '''update Message Book set message = %s where id = %s''' % (new_del, id)

            db.commit()
            print("修改成功！")

        except Exception as e:
            print("修改异常！")

    # 查询
    def search(self, cursor, db):
        try:
            print("查询数据！")
            ID = input("请输入想查询的人的id：")
            sql = '''select * from Message_Book where id = %s''' % ID
            cursor.execute(sql)
            db.commit()
            print("ID\tmessage\tname\tdate_time\t\tis_delete")
            rs = cursor.fetchall()
            for i in rs:
                print("{0}\t{1}\t{2}\t{3}\t{4}".format(i[0], i[1], i[2], i[3], i[4]))
            print("查询成功！")

        except Exception as e:
            print("查询异常！")
            raise e


def main():
    db = pymysql.connect("localhost", "root", "18311010", "test")

    cursor = db.cursor()

    new_sql = Excute()

    while True:
        print("1.添加\n2.删除\n3.修改\n4.查询\n0.退出")
        cmd = input("请选择功能（输入序号）：")
        if cmd == '1':
            new_sql.insert(cursor, db)
        elif cmd == '2':
            new_sql.delete(cursor, db)
        elif cmd == '3':
            new_sql.modify(cursor, db)
        elif cmd == '4':
            new_sql.search(cursor, db)
        elif cmd == '0':
            break

    cursor.close()
    db.close()


if __name__ == '__main__':
    main()
